package com.car.excel;
import com.car.dao.EmployeeDao;
import com.car.dao.impl.EmployeeDaoImpl;
import com.car.utils.JDBCUtil;
import com.car.utils.NumberUtils;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.HashSet;
import java.util.Set;

    /**
     * @author Layne Lei
     * @version 1.0
     * @date 2024/5/13 19:54
     * Description:
     */
    public class EmployeeExcel {

        /**
         * 加载Excel文件 并将数据写入Mysql中
         *
         * @param fileUrl 文件路径 如：excel/员工表.xlsx
         * @throws Exception 异常
         */
        public static void loadExcelAndWriteInMySQL(String fileUrl) throws Exception {
            InputStream inputStream = new FileInputStream(fileUrl);
            BufferedInputStream bufferedInputStream = new BufferedInputStream(inputStream);
            XSSFWorkbook workbook = new XSSFWorkbook(bufferedInputStream);
            XSSFSheet sheet = workbook.getSheet("Sheet1");
            // 定义行变量
            int rowIndex = 1;
            // 查询workId 避免重复问题
            EmployeeDao employeeDao = new EmployeeDaoImpl();
            Set<String> workIds = employeeDao.findWorKIds();
            while (true) {
                XSSFRow row = sheet.getRow(rowIndex);
                // 下一行为空 退出循环
                if (row == null) {
                    break;
                }
                // 这一行第一列记录
                XSSFCell c0 = row.getCell(0);
                String username = c0.getStringCellValue();

                XSSFCell c1 = row.getCell(1);
                double age = c1.getNumericCellValue();

                XSSFCell c2 = row.getCell(2);
                String gender = c2.getStringCellValue();

                XSSFCell c4 = row.getCell(4);
                double salary = c4.getNumericCellValue();
                //判断workId是否重复
                String workId;
                while (true) {
                    workId = NumberUtils.generateWorkId();
                    if (!workIds.contains(workId)) {
                        workIds.add(workId);
                        break;
                    }
                }
                // 定义插入语句
                String insertSQL = "insert into t_employee(name,age,gender,work_id,salary) VALUE (?,?,?,?,?)";
                JDBCUtil.update(insertSQL, username, age, gender
                        , workId, salary);
                // 跳到下一行记录
                rowIndex++;
            }
        }
    }

